unit clsComissao;

interface

uses Dm,dbtables,db,sysutils,Grids,biblio,DBClient;

type
  TComissao=Class(TObject)
    private
      Com_codigo    : String;
    public
      Coluna        : String;
      est_sigla     : string;
      Com_inicial   : double;
      Com_final     : double;
      Com_comissao  : double;
      function  Gravar(p_com_codigo:String):boolean;
      function  Recuperar(var p_q:TQuery;p_com_codigo:String):boolean;
      function  Recuperar_comissao(p_coluna, p_uf:string; p_vlr:double): boolean;
      function  Excluir(p_com_codigo:String):Boolean;
      procedure Consultar(var p_q:TQuery;p_coluna:String);
      function  Retorna_Ultimo : string;
      procedure Consultartodos(var p_q:TQuery);
  end;
implementation

{ TComissao }

procedure TComissao.Consultar(var p_q: TQuery; p_coluna: String);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('Select * from Comissao where coluna=:a order by com_inicial');
  p_q.parambyname('a').asstring:=coluna;
end;

procedure TComissao.Consultartodos(var p_q: TQuery);
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('Select * from Comissao order by est_sigla, coluna');
end;

function TComissao.Excluir(p_com_codigo: String): Boolean;
var tq:TQuery;
begin
  tq              := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active       := False;
  tq.close;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('DELETE FROM Comissao WHERE com_codigo=:a');
  tq.parambyname('a').asstring := strzero(p_com_codigo,4);
  Try
    tq.execSQL;
    Result := true;
  Except
    Result := false;
  End;
  tq.close;
  tq.Free;
end;

function TComissao.Gravar(p_com_codigo: String): boolean;
var tq:TQuery;
begin
  tq              := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active       := False;
  tq.close;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('SELECT * FROM Comissao where com_codigo=:a and est_sigla=:b and coluna=:c ');
  tq.parambyname('a').asstring := strzero(p_com_codigo,4);
  tq.parambyname('b').asstring := est_sigla;
  tq.parambyname('c').asstring := coluna;
  tq.open;
  if tq.eof then begin
    tq.close;
    tq.sql.clear;
    tq.params.clear;
    tq.sql.add('INSERT INTO Comissao VALUES('+
               ':K_com_codigo, '+
               ':K_Coluna, '+
               ':K_est_sigla, '+
               ':K_Com_inicial, '+
               ':K_Com_final, '+
               ':K_Com_comissao )');
  end else begin
    tq.close;
    tq.sql.clear;
    tq.params.clear;
    tq.sql.add('UPDATE Comissao SET '+
               'Coluna =:K_Coluna, '+
               'Est_sigla=:K_est_sigla, '+
               'Com_inicial =:K_Com_inicial, '+
               'Com_final =:K_Com_final, '+
               'Com_comissao =:K_Com_comissao '+
               'WHERE com_codigo=:K_com_codigo');
  end;
  tq.parambyname('K_com_codigo').asstring  := p_com_codigo;
  tq.parambyname('K_Coluna').asstring      := Coluna;
  tq.parambyname('K_est_sigla').asstring   := est_sigla;
  tq.parambyname('K_Com_inicial').asfloat  := Com_inicial;
  tq.parambyname('K_Com_final').asfloat    := Com_final;
  tq.parambyname('K_Com_comissao').asfloat := Com_comissao;
  Try
    tq.execsql;
    result := true;
  Except
    result := false;
  End;
  tq.close;
  tq.Free;
end;

function TComissao.Recuperar(var p_q:TQuery;p_com_codigo:String): boolean;
var tq:TQuery;
begin
  p_q.sql.Clear;
  p_q.Params.Clear;
  p_q.sql.add('SELECT * FROM Comissao where com_codigo=:a');
  p_q.parambyname('a').asstring := strzero(p_com_codigo,4);
end;

function TComissao.Recuperar_comissao(p_coluna, p_uf:string; p_vlr:double): boolean;
var tq:TQuery;
begin
  tq              := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active       := False;
  tq.close;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('SELECT * FROM Comissao where coluna=:a and (com_inicial<=:b and com_final>=:c) and est_sigla=:d');
  tq.parambyname('a').asstring := p_coluna;
  tq.parambyname('b').asfloat  := p_vlr;
  tq.parambyname('c').asfloat  := p_vlr;
  tq.parambyname('d').asstring := p_uf;
  tq.open;
  if tq.eof then
    result := false
  else
    result := true;

  com_codigo  := tq.fieldbyname('com_codigo').asstring;
  Coluna      := tq.fieldbyname('Coluna').asstring;
  est_sigla   := tq.fieldbyname('est_sigla').asstring;
  Com_inicial := tq.fieldbyname('Com_inicial').asfloat;
  Com_final   := tq.fieldbyname('Com_final').asfloat;
  Com_comissao:= tq.fieldbyname('Com_comissao').asfloat;
  tq.Close;
  tq.Free;
end;

function TComissao.Retorna_Ultimo: string;
var tq      : TQuery;
    xultimo : String;
begin
  tq              := Tquery.Create(dmint);
  tq.databasename := DmInt.Db.DatabaseName;
  tq.active       := False;
  tq.sql.clear;
  tq.params.clear;
  tq.sql.add('SELECT max(com_codigo) as ultimo FROM Comissao ');
  tq.open;
  if tq.fieldbyname('ultimo').asstring = '' then
    result := '0001'
  else begin
    xultimo := tq.fieldbyname('ultimo').asstring;
    xultimo := strzero((strtoint(xultimo)+1),4);
    result  := xultimo;
  end;
  tq.close;
  tq.free;
end;

end.
